/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package BD.Modulos;
import BD.Bd;
import BD.ConnBdType;
import Interfaces.BdException;
import Interfaces.TransportInterface;
import Objects.Log_TMarca;
import Objects.Log_TProced;
import Objects.Log_TTransp;
import Objects.Log_Vehiculos;
import WS.Transporte;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;
/**
 *
 * @author armgarces
 */
public class BdTransportes extends Bd{
    private String mensj;
    /***************************************************************************/
    /********************************** BD *************************************/
    /***************************************************************************/
    /**
     * 
     */
    public BdTransportes(){
       
    }  
    /**
     * 
     * @param value
     * @return 
     */
    private String selectLoadSP(TransportInterface value){
        System.out.println("Class = " + value.getClass().getSimpleName());
        switch(value.getClass().getSimpleName()){
            case "Log_Vehiculos":
                return "{call sp_get_log_vehiculo_all}";
            case "Log_TMarca":
                return "{call sp_get_log_tmarca_all}";
            case "Log_TProced":
                return "{call sp_get_log_tproced_all}";
            case "Log_TTransp":
                return "{call sp_get_log_ttransp_all}";
            default:
                return "";
        }
    }
    /**
     * 
     * @param value
     * @return 
     */
    private String selectFindSP(TransportInterface value){
        System.out.println("Class = " + value.getClass().getSimpleName());
        switch(value.getClass().getSimpleName()){
            case "Log_Vehiculos":
                params = new String[]{"@find"};
                return "{call sp_get_log_vehiculo_find(?)}";
            case "Log_TMarca":
                params = new String[]{"@find"};
                return "{call sp_get_log_tmarca_find(?)}";
            case "Log_TProced":
                params = new String[]{"@find"};
                return "{call sp_get_log_tproced_find(?)}";
            case "Log_TTransp":
                params = new String[]{"@find"};
                return "{call sp_get_log_ttransp_find(?)}";
            default:
                return "";
        }
    }
    /**
     * 
     * @param value
     * @return 
     */
    private String selectCheckSP(int value){
        switch(value){
            case Transporte.CHECK_VEHICULO:
                params = new String[]{"@log_vehiculo"};
                return "{call sp_get_log_vehiculo_check(?,?)}";
            case Transporte.CHECK_TMARCA:
                params = new String[]{"@log_tmarca"};
                return "{call sp_get_log_tmarca_check(?,?)}";
            case Transporte.CHECK_TPROCED:
                params = new String[]{"@log_tproced"};
                return "{call sp_get_log_tproced_check(?,?)}";
            case Transporte.CHECK_TTRANSP:
                params = new String[]{"@log_ttransp"};
                return "{call sp_get_log_ttransp_check(?,?)}";
            default:
                return "";
        }
    }
    /**
     * 
     * @param value
     * @return 
     */
    private String selectChangeSP(TransportInterface value){
        System.out.println("Class = " + value.getClass().getSimpleName());
        switch(value.getClass().getSimpleName()){
            case "Log_Vehiculos":
                mensj = "deleteLog_Vehiculos:";
                params = new String[]{"@nro_placa","@status"};
                return "{call sp_upd_log_vehiculo_status(?,?)}";
            case "Log_TMarca":
                mensj = "deleteLog_TMarca:";
                params = new String[]{"@id_tmarca","@status"};
                return "{call sp_upd_log_tmarca_status(?,?)}";
            case "Log_TProced":
                mensj = "deleteLog_TProced:";
                params = new String[]{"@id_tproced","@status"};
                return "{call sp_upd_log_tproced_status(?,?)}";
            case "Log_TTransp":
                mensj = "deletelog_ttransp:";
                params = new String[]{"@id_tproced","@status"};
                return "{call sp_upd_log_ttransp_status(?,?)}";
            default:
                return "";
        }
    }    
    /***************************************************************************/
    /****************************** TRANSPORTES  *******************************/
    /***************************************************************************/    
    /**
     * @author MITM
     * @param value
     * @return 
     * @throws java.sql.SQLException 
     * @throws Interfaces.BdException 
     */
    public TransportInterface[] load(TransportInterface value) throws SQLException, BdException {
         try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
            if (connection != null){
                StringBuilder sqlProc = new StringBuilder();
                sqlProc.append(selectLoadSP(value));
                CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
                ResultSet result = cstmt.executeQuery();            
                List<TransportInterface> list = new ArrayList<>();
                while(result.next()) {
                   TransportInterface obj = value.getClass().newInstance();
                   obj.loadResultSet(result);
                   list.add(obj);
                }               
                return list.toArray(new TransportInterface[list.size()]);               
            }else{
                throw new BdException("Error: Connexion no activa");
            }
        }catch(IllegalAccessException | InstantiationException ie){            
            throw new BdException("Error: Problemas de Instacia!");
        } catch (SQLException e) {
            throw new BdException("Error en BD: "+e);
        }finally{
            connection.close();
        }
    }
    /**
     * @author MITM
     * @param value
     * @param find
     * @return 
     * @throws java.sql.SQLException 
     * @throws Interfaces.BdException 
     */
    public TransportInterface[] find(TransportInterface value,String find) throws SQLException, BdException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
            if (connection != null){
                StringBuilder sqlProc = new StringBuilder();
                sqlProc.append(selectFindSP(value));
                CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
                cstmt.setString(params[0], find);
                ResultSet result = cstmt.executeQuery();            
                List<TransportInterface> list = new ArrayList<>();
                while(result.next()) {
                   TransportInterface obj = value.getClass().newInstance();
                   obj.loadResultSet(result);
                   list.add(obj);
                }               
                return list.toArray(new TransportInterface[list.size()]);               
            }else{
                throw new BdException("Error: Connexion no activa");
            }
        }catch(IllegalAccessException | InstantiationException ie){            
            throw new BdException("Error: Problemas de Instacia!");
        } catch (SQLException e) {
            throw new BdException("Error en BD: "+e);
        }finally{
            connection.close();
        }
    }    
    /**
     * 
     * @param check
     * @param name
     * @return
     * @throws SQLException
     * @throws BdException 
     */
    public boolean check(int check, String name) throws SQLException, BdException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();        
            if (connection != null){
                StringBuilder sqlProc = new StringBuilder();
                sqlProc.append(selectCheckSP(check));
                CallableStatement cstmt = connection.prepareCall(sqlProc.toString());                           
                cstmt.setString(params[0], name);
                cstmt.registerOutParameter("result", java.sql.Types.INTEGER);               
                cstmt.execute();
                return cstmt.getInt("result")==1;
            }else{
                throw new BdException("Error: Connexion no activa");
            }
        } catch (SQLException e) {
            throw new BdException("Error en BD: "+e);
        }finally{
            connection.close();
        }
    }
    /**
     * 
     * @param user
     * @param value
     * @return
     * @throws BdException 
     * @throws java.sql.SQLException 
     */
    public boolean change(String user,TransportInterface value) throws BdException, SQLException {                
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
            if (connection != null){                
                //Inicia Transaccion 
                connection.setAutoCommit(false); 
                StringBuilder sqlProc = new StringBuilder();
                sqlProc.append(selectChangeSP(value));
                CallableStatement cstmt = connection.prepareCall(sqlProc.toString());                 
                cstmt.setObject(params[0], value.getId());
                int stat = 0;
                if(value.getStatus() == 0){
                    stat = 1;
                }            
                cstmt.setInt(params[1], stat );
                cstmt.execute();            
                // Auditar el proceso
                auditar(user,value.getId()+ "",mensj);
                connection.commit();
                return true; 
            }else{
                throw new BdException("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            connection.rollback();            
            throw new BdException("Error en BD: "+e);           
        }finally{
            connection.close();
        }
    }         
    /**
     * 
     * @param user
     * @param operacion Determina el Proceso que se va a ejecutar 1 INSERT, 2 UPDATE
     * @param value
     * @return
     * @throws SQLException 
     * @throws Interfaces.BdException 
     */
    public boolean saveTMarca(String user,  int operacion, Log_TMarca value)   throws SQLException, BdException {        
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open(); 
            if (connection != null){
                //Inicia Transaccion 
                connection.setAutoCommit(false);
                StringBuilder sql = new StringBuilder();            
                mensj = "";            
                switch(operacion){
                    case 1:
                        sql.append("{call sp_ins_log_tmarca(?, ?)}");
                        mensj = "Nuevo Tipo Marca:";
                        break;
                    case 2:
                        sql.append("{call sp_upd_log_tmarca_basic(?, ?, ?)}");
                        mensj = "Actualizando Tipo Marca:";
                        break;
                }
                CallableStatement cstmt = connection.prepareCall(sql.toString());
                switch(operacion){
                    case 1:
                        cstmt.setString("@nombre"       , value.getNombre().toUpperCase());
                        cstmt.setString("@abrev"        , value.getAbrev().toUpperCase());
                        break;
                    case 2:
                        cstmt.setInt("@id_tmarca"       , value.getIdTMarca());
                        cstmt.setString("@nombre"       , value.getNombre().toUpperCase());
                        cstmt.setString("@abrev"        , value.getAbrev().toUpperCase());
                        break;
                }
                cstmt.execute();            
                // Auditar el proceso
                auditar(user,value.getId()+ "",mensj);
                connection.commit();
                return true;
            }else{
                throw new BdException("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            connection.rollback();            
            throw new BdException("Error en BD: "+e);           
        }finally{
            connection.close();
        }
    }
    /**
     * 
     * @param user
     * @param operacion Determina el Proceso que se va a ejecutar 1 INSERT, 2 UPDATE
     * @param value
     * @return
     * @throws SQLException 
     * @throws Interfaces.BdException 
     */
    public boolean saveTProced(String user, int operacion, Log_TProced value)  throws SQLException, BdException {        
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();        
            if (connection != null){
                //Inicia Transaccion 
                connection.setAutoCommit(false);
                StringBuilder sql = new StringBuilder();            
                mensj = "";
                switch(operacion){
                    case 1:
                        sql.append("{call sp_ins_log_tproced(?, ?)}");
                        mensj = "Nuevo Tipo Proced:";
                        break;
                    case 2:
                        sql.append("{call sp_upd_log_tproced_basic(?, ?, ?)}");
                        mensj = "Actualizando Tipo Proced:";
                        break;
                }
                CallableStatement cstmt = connection.prepareCall(sql.toString());
                switch(operacion){
                    case 1:
                        cstmt.setString("@nombre"       , value.getNombre().toUpperCase());
                        cstmt.setString("@abrev"        , value.getAbrev().toUpperCase());
                        break;
                    case 2:
                        cstmt.setInt("@id_tproced"      , value.getIdTProced());
                        cstmt.setString("@nombre"       , value.getNombre().toUpperCase());
                        cstmt.setString("@abrev"        , value.getAbrev().toUpperCase());
                        break;
                }
                cstmt.execute();            
                // Auditar el proceso
                auditar(user,value.getIdTProced()+ "",mensj);            
                connection.commit();
                return true;
            }else{
                throw new BdException("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            connection.rollback();            
            throw new BdException("Error en BD: "+e);           
        }finally{
            connection.close();
        }
    }   
    /**
     * 
     * @param user
     * @param operacion Determina el Proceso que se va a ejecutar 1 INSERT, 2 UPDATE
     * @param value
     * @return
     * @throws SQLException 
     * @throws Interfaces.BdException 
     */
    public boolean saveTTransp(String user, int operacion, Log_TTransp value)  throws SQLException, BdException {               
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open(); 
            if (connection != null){
                //Inicia Transaccion 
                connection.setAutoCommit(false);
                StringBuilder sql = new StringBuilder();            
                mensj = "";
                switch(operacion){
                    case 1:
                        sql.append("{call sp_ins_log_ttransp(?, ?)}");
                        mensj = "Nuevo Tipo Transp:";
                        break;
                    case 2:
                        sql.append("{call sp_upd_log_ttransp_basic(?, ?, ?)}");
                        mensj = "Actualizando Tipo Transp:";
                        break;
                }
                CallableStatement cstmt = connection.prepareCall(sql.toString());
                switch(operacion){
                    case 1:
                        cstmt.setString("@nombre"       , value.getNombre().toUpperCase());
                        cstmt.setString("@abrev"        , value.getAbrev().toUpperCase());
                        break;
                    case 2:
                        cstmt.setInt("@id_ttransp"       , value.getIdTTransp());
                        cstmt.setString("@nombre"        , value.getNombre().toUpperCase());
                        cstmt.setString("@abrev"         , value.getAbrev().toUpperCase());
                        break;
                }
                cstmt.execute();            
                // Auditar el proceso
                auditar(user,value.getIdTTransp()+ "",mensj);
                connection.commit();
                return true;
            }else{
                throw new BdException("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            connection.rollback();            
            throw new BdException("Error en BD: "+e);           
        }finally{
            connection.close();
        }
    }   
    /**
     * 
     * @param user
     * @param operacion Determina el Proceso que se va a ejecutar 1 INSERT, 2 UPDATE
     * @param value
     * @return
     * @throws SQLException 
     * @throws Interfaces.BdException 
     */
    public boolean saveVehiculos(String user,int operacion,Log_Vehiculos value)throws SQLException, BdException {                       
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open(); 
            if (connection != null){
                //Inicia Transaccion 
                connection.setAutoCommit(false);
                StringBuilder sql = new StringBuilder();            
                mensj = "";            
                switch(operacion){
                    case 1:
                        sql.append("{call sp_ins_log_vehiculo_data(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");
                        mensj = "Nuevo Vehiculo:";
                        break;
                    case 2:
                        sql.append("{call sp_upd_log_vehiculo_basic(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");
                        mensj = "Actualizando Vehiculo:";
                        break;
                }
                CallableStatement cstmt = connection.prepareCall(sql.toString());
                cstmt.setString("@nro_placa"           , value.getIdPlaca().toUpperCase());
                cstmt.setInt("@tmarca"                 , value.getTmarca().getIdTMarca());
                cstmt.setInt("@tproced"                , value.getTproced().getIdTProced());
                cstmt.setInt("@ttransp"                , value.getTtransp().getIdTTransp());
                cstmt.setString("@modelo"              , value.getModelo().toUpperCase());
                cstmt.setInt("@capacidad"              , value.getCapacidad());
                cstmt.setInt("@ano"                    , value.getAno());
                cstmt.setInt("@clasificacion"          , value.getClasif());
                //
                if (value.getEmpresa() != null)
                    cstmt.setString("@empresa"         , value.getEmpresa().toUpperCase());
                else
                    cstmt.setString("@empresa"         , value.getEmpresa());
                //
                cstmt.setString("@telefonos"           , value.getTelefonos());
                cstmt.setString("@celular"             , value.getCelular());
                if (value.getCorreo() != null)
                    cstmt.setString("@correo"          , value.getCorreo().toLowerCase());
                else
                    cstmt.setString("@correo"          , value.getCorreo());
                //
                if (value.getRuta_cc() != null)
                    cstmt.setString("@ruta_cc"         , value.getRuta_cc().toLowerCase());
                else
                    cstmt.setString("@ruta_cc"         , value.getRuta_cc());
                //
                if (value.getRuta_tt() != null)
                    cstmt.setString("@ruta_tt"         , value.getRuta_tt().toLowerCase());
                else
                    cstmt.setString("@ruta_tt"         , value.getRuta_tt());
                //
                if (value.getRuta_rcv() != null)
                    cstmt.setString("@ruta_rcv"        , value.getRuta_rcv().toLowerCase());
                else
                    cstmt.setString("@ruta_rcv"        , value.getRuta_rcv());
                //
                if (value.getRuta_ps() != null)
                    cstmt.setString("@ruta_ps"         , value.getRuta_ps().toLowerCase());
                else
                    cstmt.setString("@ruta_ps"         , value.getRuta_ps());
                //
                if (value.getRuta_rgt() != null)
                    cstmt.setString("@ruta_rgt"        , value.getRuta_rgt().toLowerCase());
                else
                    cstmt.setString("@ruta_rgt"        , value.getRuta_rgt());
                //
                if(value.getFec_rcv().toString().equals(LocalDate.now().toString())) 
                    cstmt.setDate("@fec_rcv"           , null);
                else
                    cstmt.setDate("@fec_rcv"           , value.getFec_rcv());
                //
                if(value.getFec_ps().toString().equals(LocalDate.now().toString())) 
                    cstmt.setDate("@fec_ps"            , null);
                else
                    cstmt.setDate("@fec_ps"            , value.getFec_ps());
                //
                if(value.getFec_rgt().toString().equals(LocalDate.now().toString())) 
                    cstmt.setDate("@fec_rgt"           , null);
                else
                    cstmt.setDate("@fec_rgt"           , value.getFec_rgt());
                //
                cstmt.setString("@nro_rgt"             , value.getNro_rgt());
                cstmt.execute();            
                // Auditar el proceso
                auditar(user,value.getIdPlaca()+ "",mensj);
                connection.commit();
                return true;
            }else{
                throw new BdException("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            connection.rollback();            
            throw new BdException("Error en BD: "+e);           
        }finally{
            connection.close();
        }
    }    
    /***************************************************************************/
    /******************************* SINGLETON *********************************/
    /***************************************************************************/    
    private static class BdHolder {
        private static final BdTransportes INSTANCE = new BdTransportes();
    }
    public static BdTransportes getInstance() {
        return BdTransportes.BdHolder.INSTANCE;
    }
}
